Imports System.Data.SqlClient

Public Class ClsSQL
    Private conn As New SqlConnection

    Sub Conectar(ByVal bd As Integer)
        If (conn.State = ConnectionState.Closed) Then
            Select Case bd
                Case 0
                    conn.ConnectionString = "Data Source=SERVIDOR;Initial Catalog=administracion;Integrated Security=True"
                Case 1
                    conn.ConnectionString = "Data Source=SERVIDOR;Initial Catalog=Corbol;Integrated Security=True"
                    'conn.ConnectionString = "Data Source=SERVIDOR;Initial Catalog=Corbol;Integrated Security=SSPI;"
                Case 2
                    conn.ConnectionString = "Data Source=SERVIDOR;Initial Catalog=importacion;Integrated Security=True"
                Case 3
                    conn.ConnectionString = "Data Source=SERVIDOR;Initial Catalog=corbol09;Integrated Security=True"
                Case 4
                    conn.ConnectionString = "Data Source=SERVIDOR;Initial Catalog=corbol2010;Integrated Security=True"
                Case 5
                    conn.ConnectionString = "Data Source=SERVIDOR;Initial Catalog=pruebas;Integrated Security=True"
                Case 6
                    conn.ConnectionString = "Data Source=SERVIDOR;Initial Catalog=corbol2011;Integrated Security=True"
            End Select

            Try
                conn.Open()
            Catch ex As Exception
                MsgBox("No se pudo Conectar con la Base de Datos")
            End Try
        End If
    End Sub

    Sub Conectar(ByVal bd As String)
        If (conn.State = ConnectionState.Closed) Then
            conn.ConnectionString = "Data Source=SERVIDOR;Initial Catalog=" & bd & ";Integrated Security=True"

            Try
                conn.Open()
            Catch ex As Exception
                MsgBox("No se pudo Conectar con la Base de Datos")
            End Try
        End If
    End Sub

    Sub Desconectar()
        conn.Close()
    End Sub

    Public Function Ejecutar(ByVal strsql As String) As Boolean
        Dim com As New SqlCommand
        com.Connection = conn
        com.CommandText = strsql
        Try
            com.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("No se pudo Realizar la Operacion " + ex.ToString)
        End Try
        Return (True)
    End Function

    Public Function EjecutarGuardarImagen(ByVal strsql As String, ByVal datos() As Byte) As Boolean
        Dim com As New SqlCommand
        com.Connection = conn
        com.CommandText = strsql
        com.Parameters.Add("@imagen", SqlDbType.Image).Value = datos
        Try
            com.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("No se pudo Realizar la Operacion " + ex.ToString)
        End Try
        Return (True)
    End Function

    Public Function EjecutarGuardarImagenes(ByVal strsql As String, ByVal datos1() As Byte, ByVal datos2() As Byte, ByVal datos3() As Byte, ByVal datos4() As Byte, ByVal datos5() As Byte) As Boolean
        Dim com As New SqlCommand
        com.Connection = conn
        com.CommandText = strsql
        com.Parameters.Add("@imagen1", SqlDbType.Image).Value = datos1
        com.Parameters.Add("@imagen2", SqlDbType.Image).Value = datos2
        com.Parameters.Add("@imagen3", SqlDbType.Image).Value = datos3
        com.Parameters.Add("@imagen4", SqlDbType.Image).Value = datos4
        com.Parameters.Add("@imagen5", SqlDbType.Image).Value = datos5
        Try
            com.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("No se pudo Realizar la Operacion " + ex.ToString)
        End Try
        Return (True)
    End Function

    Public Function EjecutarGuardarImagenes6(ByVal strsql As String, ByVal datos1() As Byte, ByVal datos2() As Byte, ByVal datos3() As Byte, ByVal datos4() As Byte, ByVal datos5() As Byte, ByVal datos() As Byte) As Boolean
        Dim com As New SqlCommand
        com.Connection = conn
        com.CommandText = strsql
        com.Parameters.Add("@imagen1", SqlDbType.Image).Value = datos1
        com.Parameters.Add("@imagen2", SqlDbType.Image).Value = datos2
        com.Parameters.Add("@imagen3", SqlDbType.Image).Value = datos3
        com.Parameters.Add("@imagen4", SqlDbType.Image).Value = datos4
        com.Parameters.Add("@imagen5", SqlDbType.Image).Value = datos5
        com.Parameters.Add("@logo", SqlDbType.Image).Value = datos
        Try
            com.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("No se pudo Realizar la Operacion " + ex.ToString)
        End Try
        Return (True)
    End Function

    Public Function EjecutarScalar(ByVal strsql As String) As String
        Dim com As New SqlCommand
        Dim last As String = ""
        com.Connection = conn
        com.CommandText = strsql
        Try
            last = com.ExecuteScalar()
        Catch ex As Exception
            MsgBox("No se pudo Realizar la Operacion " + ex.ToString)
        End Try
        Return (last)
    End Function

    Function TraerTabla(ByVal tabla As String, ByVal campos As String, Optional ByVal parametros As String = "1=1") As DataTable
        Dim Ada As SqlDataAdapter
        
        Ada = New SqlDataAdapter("SELECT " + campos + " FROM " + tabla + " WHERE " + parametros, conn)
        Dim ds As New DataSet
        Try
            Ada.Fill(ds, tabla)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        Return ds.Tables(tabla)
    End Function

    ', Optional ByVal parametros As String = "1=1"

    Function EjecutarSql(ByVal tabla As String, ByVal Sql As String) As DataTable
        Dim Ada As SqlDataAdapter

        Ada = New SqlDataAdapter(Sql, conn)
        Dim ds As New DataSet
        Try
            Ada.Fill(ds, tabla)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        Return ds.Tables(tabla)
    End Function

    Function TraerTablaDs(ByVal tabla As String, ByVal campos As String, Optional ByVal parametros As String = "1=1") As DataSet
        Dim Ada As SqlDataAdapter
        Ada = New SqlDataAdapter("SELECT " + campos + " FROM " + tabla + " WHERE " + parametros, conn)
        Dim ds As New DataSet
        Ada.Fill(ds, tabla)
        Return ds
    End Function
    Function BuscadorDs(ByVal tabla As String, Optional ByVal strsql As String = "1=1") As DataSet
        Dim Ada As SqlDataAdapter
        Ada = New SqlDataAdapter("SELECT * FROM " + tabla + " " + strsql, conn)
        Dim ds As New DataSet
        Ada.Fill(ds, tabla)
        Return ds
    End Function
    Function Buscador(ByVal tabla As String, Optional ByVal strsql As String = "1=1") As DataTable
        Dim Ada As SqlDataAdapter
        Ada = New SqlDataAdapter("SELECT * FROM " + tabla + " " + strsql, conn)
        Dim ds As New DataSet
        Try
            Ada.Fill(ds, tabla)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        Return ds.Tables(tabla)
    End Function
    'COMIENZA UNA TRANSACCION
    Public Sub ComenzarTran()
        Dim Comando As New SqlCommand
        'Conectar()
        'Me.Transaccion = True
        Comando.Connection = conn
        Comando.CommandText = "BEGIN TRANSACTION"
        Comando.ExecuteNonQuery()
    End Sub
    'CONFIRMA UN TRANSACCION
    Public Sub ConfirmarTran()
        Dim Comando As New SqlCommand
        Comando.Connection = conn
        Comando.CommandText = "COMMIT TRANSACTION"
        Comando.ExecuteNonQuery()
        ' Transaccion = False
        'DesConectar()
    End Sub
    'DESHACE UN TRANSACCION
    Public Sub DeshacerTran()
        Dim Comando As New SqlCommand
        Comando.Connection = conn
        Comando.CommandText = "ROLLBACK TRANSACTION"
        Comando.ExecuteNonQuery()
        'Transaccion = False
        ' DesConectar()
    End Sub
    'BLOQUEA UN TRANSACCION
    'Public Sub BloquearTran()
    '    Dim Comando As New SqlCommand
    '    Comando.Connection = conn
    '    Comando.CommandText = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED"
    '    Comando.ExecuteNonQuery()
    'End Sub
End Class
